﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_service_sel_hierall]
	@SERVICE_ID int
AS

DECLARE @TMP_HEIRALL TABLE ([SERVICE_ID] INT, [SERVICE_NAME] VARCHAR(100), [PARENT_ID] INT, [SERVICE_TYPE] INT, [SERVICE_TYPE_NAME] VARCHAR(100))

BEGIN
	 -- WALK UP THE TREE.
	 WITH nested_Up AS
	(
		SELECT A.[SERVICE_ID], A.[SERVICE_NAME], A.[PARENT_ID], B.[SERVICE_TYPE], B.[SERVICE_TYPE_NAME]
		  FROM flcr.[SERVICE] A
		 INNER JOIN flcr.[SERVICE_TYPE] B
		    ON A.[SERVICE_TYPE] = B.[SERVICE_TYPE] 
		WHERE A.[SERVICE_ID] = @SERVICE_ID

		UNION ALL

		SELECT B.[SERVICE_ID], B.[SERVICE_NAME], B.[PARENT_ID], B.[SERVICE_TYPE], C.[SERVICE_TYPE_NAME] 
		  FROM nested_Up a
		 INNER JOIN flcr.[SERVICE] B
			ON A.[PARENT_ID] = B.[SERVICE_ID]
		 INNER JOIN flcr.[SERVICE_TYPE] C
		    ON B.[SERVICE_TYPE] = C.[SERVICE_TYPE] 
	)
	INSERT INTO @TMP_HEIRALL 
	SELECT * FROM nested_Up;
	
	-- WALK DOWN THE TREE.
	 WITH nested_Down AS
	(
		SELECT A.[SERVICE_ID], A.[SERVICE_NAME], A.[PARENT_ID], B.[SERVICE_TYPE], B.[SERVICE_TYPE_NAME]
		  FROM flcr.[SERVICE] A
		 INNER JOIN flcr.[SERVICE_TYPE] B
		    ON A.[SERVICE_TYPE] = B.[SERVICE_TYPE] 
		WHERE A.[SERVICE_ID] = @SERVICE_ID

		UNION ALL

		SELECT B.[SERVICE_ID], B.[SERVICE_NAME], B.[PARENT_ID], B.[SERVICE_TYPE], C.[SERVICE_TYPE_NAME] 
		  FROM nested_Down a
		 INNER JOIN flcr.[SERVICE] b
			ON A.[SERVICE_ID] = B.[PARENT_ID]
		 INNER JOIN flcr.[SERVICE_TYPE] C
		    ON B.[SERVICE_TYPE] = C.[SERVICE_TYPE]			
	)
	INSERT INTO @TMP_HEIRALL 
	SELECT * FROM nested_Down;
	
	-- RETURN FULL HEIRARCHY
	SELECT DISTINCT * 
	  FROM @TMP_HEIRALL
	 ORDER BY [PARENT_ID]
	
END